"""
数据库模块
使用SQLite存储测试用例
"""
import sqlite3
import json
from datetime import datetime
from typing import Optional, List, Dict


class Database:
    """SQLite数据库操作类"""
    
    def __init__(self, db_path='test_cases.db'):
        self.db_path = db_path
        self.init_db()
    
    def get_connection(self):
        """获取数据库连接"""
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        return conn
    
    def init_db(self):
        """初始化数据库表结构"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        # 创建测试用例表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS test_cases (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                module TEXT,
                domain TEXT NOT NULL,
                url_path TEXT NOT NULL,
                method TEXT DEFAULT 'GET',
                headers TEXT,
                body TEXT,
                full_url TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 检查并添加module列（如果表已存在但没有该列）
        try:
            cursor.execute("SELECT module FROM test_cases LIMIT 1")
        except sqlite3.OperationalError:
            # 列不存在，添加它
            cursor.execute("ALTER TABLE test_cases ADD COLUMN module TEXT")
        
        # 创建执行记录表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS execution_records (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                case_id INTEGER NOT NULL,
                status_code INTEGER,
                response_body TEXT,
                response_headers TEXT,
                execution_time REAL,
                success BOOLEAN,
                error_message TEXT,
                executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (case_id) REFERENCES test_cases (id)
            )
        ''')
        
        # 创建参数表（用于参数化）
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS parameters (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                case_id INTEGER NOT NULL,
                param_name TEXT NOT NULL,
                param_value TEXT NOT NULL,
                json_path TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (case_id) REFERENCES test_cases (id),
                UNIQUE(case_id, param_name)
            )
        ''')
        
        conn.commit()
        conn.close()
    
    def create_case(self, title: str, domain: str, url_path: str, method: str,
                    headers: dict, body: dict, full_url: str, module: str = None) -> int:
        """
        创建测试用例
        返回: case_id
        """
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            INSERT INTO test_cases (title, module, domain, url_path, method, headers, body, full_url)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            title,
            module,
            domain,
            url_path,
            method,
            json.dumps(headers),
            json.dumps(body),
            full_url
        ))
        
        case_id = cursor.lastrowid
        conn.commit()
        conn.close()
        
        return case_id
    
    def get_case(self, case_id: int) -> Optional[Dict]:
        """获取单个测试用例"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('SELECT * FROM test_cases WHERE id = ?', (case_id,))
        row = cursor.fetchone()
        conn.close()
        
        if row:
            return self._row_to_dict(row)
        return None
    
    def get_all_cases(self) -> List[Dict]:
        """获取所有测试用例"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('SELECT * FROM test_cases ORDER BY id DESC')
        rows = cursor.fetchall()
        conn.close()
        
        return [self._row_to_dict(row) for row in rows]
    
    def update_case(self, case_id: int, title: str, domain: str, url_path: str,
                   method: str, headers: dict, body: dict) -> bool:
        """更新测试用例"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            UPDATE test_cases
            SET title = ?, domain = ?, url_path = ?, method = ?,
                headers = ?, body = ?, updated_at = ?
            WHERE id = ?
        ''', (
            title,
            domain,
            url_path,
            method,
            json.dumps(headers),
            json.dumps(body),
            datetime.now().isoformat(),
            case_id
        ))
        
        success = cursor.rowcount > 0
        conn.commit()
        conn.close()
        
        return success
    
    def delete_case(self, case_id: int) -> bool:
        """删除测试用例"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('DELETE FROM test_cases WHERE id = ?', (case_id,))
        
        success = cursor.rowcount > 0
        conn.commit()
        conn.close()
        
        return success
    
    def save_execution_record(self, case_id: int, status_code: int,
                            response_body: str, response_headers: dict,
                            execution_time: float, success: bool,
                            error_message: str = None) -> int:
        """保存执行记录"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            INSERT INTO execution_records
            (case_id, status_code, response_body, response_headers,
             execution_time, success, error_message)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', (
            case_id,
            status_code,
            response_body,
            json.dumps(response_headers) if response_headers else None,
            execution_time,
            success,
            error_message
        ))
        
        record_id = cursor.lastrowid
        conn.commit()
        conn.close()
        
        return record_id
    
    def get_execution_records(self, case_id: int, limit: int = 10) -> List[Dict]:
        """获取执行记录"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT * FROM execution_records
            WHERE case_id = ?
            ORDER BY executed_at DESC
            LIMIT ?
        ''', (case_id, limit))
        
        rows = cursor.fetchall()
        conn.close()
        
        return [self._row_to_dict(row) for row in rows]
    
    def save_parameter(self, case_id: int, param_name: str, param_value: str,
                      json_path: str = None) -> bool:
        """保存参数"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            INSERT OR REPLACE INTO parameters
            (case_id, param_name, param_value, json_path)
            VALUES (?, ?, ?, ?)
        ''', (case_id, param_name, param_value, json_path))
        
        conn.commit()
        conn.close()
        
        return True
    
    def get_parameters(self, case_id: int) -> List[Dict]:
        """获取指定case的所有参数"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT * FROM parameters WHERE case_id = ?
        ''', (case_id,))
        
        rows = cursor.fetchall()
        conn.close()
        
        return [self._row_to_dict(row) for row in rows]
    
    def get_all_parameters(self) -> Dict[str, str]:
        """获取所有参数，返回 {param_name: param_value} 字典"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('SELECT param_name, param_value FROM parameters')
        rows = cursor.fetchall()
        conn.close()
        
        return {row['param_name']: row['param_value'] for row in rows}
    
    def _row_to_dict(self, row) -> Dict:
        """将sqlite3.Row转换为字典"""
        result = {}
        for key in row.keys():
            value = row[key]
            # 尝试解析JSON字段
            if key in ['headers', 'body', 'response_headers'] and value:
                try:
                    result[key] = json.loads(value)
                except:
                    result[key] = value
            else:
                result[key] = value
        return result

